﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using EShop.Common;
using EShop.BussinessObjects;
using MySql.Data.MySqlClient;

namespace EShop.DataAccess
{
    public class CDACompanyConstant
    {
        public static List<CompanyConstant> Select(CompanyConstant companyconstant)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();

            string NotEffective=((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString();

            string _cmdText =
            " SELECT cc.* " +
            " FROM companyconstant cc " +
            " WHERE ((cc.ID = " + companyconstant.ID.ToString() + ") OR (" + companyconstant.ID.ToString() + " = " + NotEffective+ ")) " +
            "   AND ((cc.CompanyID=" + companyconstant.Company.ID.ToString() + ")OR(" + companyconstant.Company.ID.ToString() +"="+NotEffective+ "))" +
            "   AND ((cc.constantID=" + companyconstant.Constants.ID.ToString() + ")OR(" + companyconstant.Constants.ID.ToString() + "=" + NotEffective + "))" +
            "ORDER BY cc.ID";

            MySqlCommand cmd = new MySqlCommand(_cmdText, con);
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);


            List<CompanyConstant> companyconstantsList = new List<CompanyConstant>();

            CompanyConstant _companyconstant;
            foreach (DataRow row in tbl.Rows)
            {
                _companyconstant = new CompanyConstant();
                
                _companyconstant.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));
                _companyconstant.Company = new Company();
                _companyconstant.Company.ID = (row["CompanyID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["CompanyID"]));

                _companyconstant.Sequence = (row["Sequence"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["Sequence"]));

                _companyconstant.Constants = new Constants();
                _companyconstant.Constants.ID = (row["ConstantID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ConstantID"]));

                _companyconstant.Value = (row["Value"].ToString() == "" ? null : row["Value"].ToString());
                _companyconstant.Value2 = (row["Value2"].ToString() == "" ? null : row["Value2"].ToString());
                _companyconstant.Value3 = (row["Value3"].ToString() == "" ? null : row["Value3"].ToString());

                companyconstantsList.Add(_companyconstant);
            }

            con.Close();
            return companyconstantsList;
        }
    }
}